<?php
/**
 * 課表節次時間
 * @author cik
 *
 */
class Default_Model_Upgrade_SectionTime extends Default_Model_Upgrade_Core
{
    protected $_table = 'section_time';

    protected $_targetTable = array('semester_section','semester_teacher_course','semester_timetable');

    function upgrade (){
        if ($this->isUpgrade())
        return 'isUpgrde';
        else {
            // 先改為 MYISM
            $query = "ALTER TABLE `semester_teacher_course` DROP FOREIGN KEY `semester_teacher_course_ibfk_1` ;";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_teacher_course`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_timetable` DROP FOREIGN KEY `semester_timetable_ibfk_2` ;";
            $this->_newdbPdo->exec($query);

            $query ="ALTER TABLE `semester_timetable`
  DROP FOREIGN KEY  `semester_timetable_ibfk_1` ";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_timetable`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_section`  ENGINE = MYISAM";
            $this->_newdbPdo->exec($query);

            $hasData = 0 ;
            $query = "SELECT COUNT(*)  AS cc FROM semester_section";
            $res = $this->_newdbPdo->query($query)->fetch();
            $hasData = $res['cc'];

            $view = Zend_Controller_Action_HelperBroker::getStaticHelper('viewRenderer')->view;
            // TODO 如果未設年級時會有錯誤
            $time_arr = $view->year_name;
            $query = "SELECT * FROM section_time";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            $temp_section_arr = array();
            foreach($res as $row) {
                $arr = array();
                $arr['year'] = $row['year'];
                $arr['semester'] = $row['semester'];
                $arr['section'] = "第".$row['sector']."節";
                $temp= explode("-",$row['stime']);
                $arr['time_start'] = $temp[0];
                $arr['time_end'] = $temp[1];
                $arr['enable']  = 'Y';
                $arr['common'] ='Y';
                if ($hasData) {
                    $find = $this->_newdbPdo->query("SELECT year FROM semester_section
				  WHERE  year ='{$arr['year']}'
				  AND semester ='{$arr['semester']}'
				  AND section ='{$arr['section']}'
				  ");
                    if ($find->rowCount() > 0)
                    continue;
                }
                foreach($time_arr as $id=>$val) {
                    $arr['grade'] = $id;
                    $sn = $this->fromArray('semester_section',$arr);
                    $temp_section_arr[$arr['year']][$arr['semester']][$id][$row['sector']] = $sn;
                }
            }
            //            print_r($temp_section_arr);exit;
            // 預設上課時間
            if (count($time_arr)==3) // 國中
            $default_time = array(
            1=>array("08:25","09:10"),
            2=>array("09:20","10:05"),
            3=>array("10:15","11:00"),
            4=>array("11:10","11:55"),
            5=>array("13:15","14:00"),
            6=>array("14:10","14:55"),
            7=>array("15:10","15:55"));
            else
            $default_time = array(
            1=>array("08:40","09:10"),
            2=>array("09:20","10:10"),
            3=>array("10:30","11:10"),
            4=>array("11:20","12:00"),
            5=>array("13:30","14:10"),
            6=>array("14:20","15:00"),
            7=>array("15:10","15:50"));

            // 排課
            $query = "SELECT * FROM score_course WHERE ss_id>0";
            $res = $this->_sfs3Pdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                // 如果沒有節次設定
                // 新增預設設定
                if (!$section_sn = $temp_section_arr[$row['year']][$row['semester']][$row['class_year']][$row['sector']]) {
                    $arr2 = array();
                    $arr2['year'] = $row['year'];
                    $arr2['semester'] = $row['semester'];
                    $arr2['sort'] = $row['sector'];
                    //$arr2['grade'] = $row['class_year'] ;
                    $arr2['section'] = "第".$row['sector']."節";
                    $arr2['time_start'] = $default_time[$row['sector']][0];
                    $arr2['time_end'] = $default_time[$row['sector']][1];
                    $arr2['enable']  = 'Y';
                    $arr2['common'] ='Y';
                    $find = false;
                    foreach($time_arr as $id=>$val) {
                        $arr2['grade'] = $id;
                        if ($hasData) {
                            $find = $this->_newdbPdo->query("SELECT year FROM semester_section
				  WHERE  year ='{$arr2['year']}'
				  AND semester ='{$arr2['semester']}'
				  AND section ='{$arr2['section']}'
				   AND sort ='{$arr2['sort']}'
				   AND grade ='{$arr2['grade']}'
				  ");
                            if ($find->rowCount() > 0)
                            continue;
                        }
                        $section_sn = $this->fromArray('semester_section',$arr2);

                        $temp_section_arr[$arr2['year']][$arr2['semester']][$id][$row['sector']] = $section_sn;
                    }
                    $section_sn = $temp_section_arr[$row['year']][$row['semester']][$row['class_year']][$row['sector']];
                }

                $arr['class_sn'] = $row['class_name'];
                $arr['subject_sn'] = $row['ss_id'];
                $arr['section_sn'] = $section_sn;
                $arr['day'] = $row['day'];
                $arr['teacher_sn'] = $row['teacher_sn'] ;
                $this->fromArray('semester_timetable',$arr);

            }

//
//            echo "<pre>";
//            print_r($temp_section_arr);exit;
            // 加入配課記錄
            $query = " SELECT year,semester,grade,teacher_sn ,class_sn,subject_sn,count(*) AS cc FROM
			 semester_view_section_timetable GROUP BY teacher_sn,class_sn,subject_sn ";
            $res = $this->_newdbPdo->query($query)->fetchAll(PDO::FETCH_ASSOC);
            foreach($res as $row) {
                $arr = array();
                $arr['year'] = $row['year'];
                $arr['semester'] = $row['semester'];
//                $arr['class_id'] = sprintf("%d%02d",$row['grade'],$row['class_sn']);
                $arr['grade'] = $row['grade'];
                $arr['class_sn'] = $row['class_sn'];
                $arr['subject_sn'] = $row['subject_sn'];
                $arr['teacher_sn'] = $row['teacher_sn'];
                $arr['number'] = $row['cc'];
//                $find = $this->_newdbPdo->query("SELECT year FROM semester_teacher_course
//				  WHERE  year ='{$arr['year']}'
//				  AND semester ='{$arr['semester']}'
//				  AND grade ='{$row['grade']}'
//				  AND class_sn ='{$row['class_sn']}'
//				  AND subject_sn ='{$arr['subject_sn']}'
//				  AND teacher_sn ='{$arr['teacher_sn']}'
//				  ");
//
//                if ($find->rowCount() > 0)
//                continue;
//                if ($row['subject_sn'])
                $this->fromArray('semester_teacher_course',$arr);
            }
            // 更改為 INNOD
            $query = "ALTER TABLE `semester_section`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_teacher_course`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_teacher_course` ADD FOREIGN KEY ( `subject_sn` ) REFERENCES `semester_course` (
`sn`) ON DELETE CASCADE ON UPDATE CASCADE ; ";
            $this->_newdbPdo->exec($query);

            $query = "ALTER TABLE `semester_timetable`  ENGINE = INNODB";
            $this->_newdbPdo->exec($query);

            $query ="ALTER TABLE `semester_timetable`
  ADD CONSTRAINT `semester_timetable_ibfk_1` FOREIGN KEY (`section_sn`) REFERENCES `semester_section` (`sn`) ON DELETE CASCADE ON UPDATE CASCADE;";
            $this->_newdbPdo->exec($query);

//
//            $query = "ALTER TABLE `semester_timetable` ADD FOREIGN KEY ( `section_sn` ) REFERENCES `semester_section` (
//`sn`) ON DELETE CASCADE ON UPDATE CASCADE ;";
//            $this->_newdbPdo->exec($query);


            $log['memo'] = 'section_time 升級';
            $log['upgrade_time']	= date("Y-m-d h:i:s");

            $this->finishUpgrade($log);

        }
    }
}